//
//  DataBaseHandle.m
//  Movie
//
//  Created by cjy on 16/4/1.
//  Copyright © 2016年 HY. All rights reserved.
//

#import "DataBaseHandle.h"

#import <sqlite3.h>
#import "MovieModel.h"

@implementation DataBaseHandle


+ (instancetype)shareDataBaseHandle {
    
    // 在静态区,只初始化一次
    static DataBaseHandle *dataBase = nil;
    
    if (dataBase == nil) {
        dataBase = [[DataBaseHandle alloc] init];
        
    }
    
    return dataBase;
    
}

sqlite3 *db;

#pragma mark - 打开数据库

//定义 SQLite 结构体(对象)

- (void)open {
    
    // API sqlite3_open
    
    if (db) {
        NSLog(@"数据库已经开启!!!");
        return;
    }
    
    
    NSString *file = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"Movie.sqlite"];
    NSLog(@"%@", file);
    
    int result = sqlite3_open(file.UTF8String, &db);  //utf8string 将字符串反转为 C 字符串
    if (result == SQLITE_OK) {
        
        NSLog(@"数据库打开成功");
        
    } else {
        
        NSLog(@"数据库打开失败, code = %d", result);
        
        
    }
    
    
}

#pragma mark - 创建表格

- (void) createTable {
    
    // API sqlite3_exec
    
    //创建 SQL 语句
    NSString *createTableSQL = @"CREATE TABLE IF NOT EXISTS movie(number INTEGER PRIMARY KEY AUTOINCREMENT, title text, coverForDetail text, category text, playUrl text)";
    
    
    
    
    int result = sqlite3_exec(db, createTableSQL.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"创建表格成功");
        
    }else {
        
        NSLog(@"创建表格失败. code:%d", result);
        
    }
    
}


#pragma mark - 插入数据

- (void)insertStudent:(MovieModel *) page {
    
    // API: sqlite3_exec
    
    NSString *insertSQL = [NSString stringWithFormat:@"INSERT into movie(title,coverForDetail, category,playUrl) VALUES('%@', '%@', '%@','%@')", page.title, page.coverForDetail, page.category,page.playUrl];
    int result = sqlite3_exec(db, insertSQL.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"添加数据完成");
    }else {
        
        NSLog(@"添加数据失败 code%d", result);
        
    }
    
    
}
#pragma mark - 删除数据

-(void)deleteWithModel:(MovieModel *)model {
    //API sqlite3_exec
    
    NSString *deleteSQL = [NSString stringWithFormat:@"DELETE FROM movie WHERE title = '%@'", model.title];
    
    int result = sqlite3_exec(db, deleteSQL.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else {
        
        NSLog(@"删除失败 code:%d", result);
        
    }
}

#pragma mark - 查询数据

- (NSArray *)select {
    
    //存放符合条件的数据(元素是 model 对象 )
    NSMutableArray *array = [NSMutableArray array];
    
    // sql 语句
    
    NSString *selectSQL = [NSString stringWithFormat:@"SELECT * FROM movie"];
    
    // API stmt 结构体, sqlite3_prepareV2, sqlite3_step, sqlite3_column
    
    // 声明一个结构体 stmt 对象
    sqlite3_stmt *stmt = nil;
    
    int result = sqlite3_prepare_v2(db, selectSQL.UTF8String, -1, &stmt, nil); // -1 查询所有对象
    
    if (result == SQLITE_OK) {
        
        NSLog(@"准备就绪, 查询中...");
        // 每行都执行查询语句
        
        while(sqlite3_step(stmt) == SQLITE_ROW) {
            
            // 如果查询条件匹配, 通过 sqlite3_column 函数簇  取出值.
            
            // 取出哪一列的数据
            const unsigned char *title = sqlite3_column_text(stmt, 1);
            const unsigned char *coverForDetail = sqlite3_column_text(stmt, 2);
            const unsigned char *category = sqlite3_column_text(stmt, 3);
            const unsigned char *playUrl  =
            sqlite3_column_text(stmt, 4);
            
            //创建 model 对象,赋值之后放入数组中
            
            MovieModel *firstPage = [[MovieModel alloc] init];
            
            firstPage.title = [NSString stringWithUTF8String:(const char *) title];
            firstPage.coverForDetail = [NSString stringWithUTF8String:(const char *) coverForDetail];
            firstPage.category = [NSString stringWithUTF8String:(const char *) category];
            
            firstPage.playUrl = [NSString stringWithUTF8String:(const char *) playUrl];
            

            [array addObject:firstPage];
            
        }
        
        //销毁对象
        sqlite3_finalize(stmt);
        
    }else {
        
        NSLog(@"不能正常查询 code %d", result);
        
        //销毁对象
        sqlite3_finalize(stmt);
    }
    return array;
}

#pragma mark - 关掉数据库

- (void)closeDB {
    
    //API sqlite3_close
    
    int result = sqlite3_close(db);
    
    if (result == SQLITE_OK) {
        NSLog(@"数据库关闭");
        db = nil;
    }else {
        NSLog(@"数据库关闭失败 code:%d", result);
    }
}







@end
